﻿using System;
using System.Data.OleDb;
using System.IO;
using System.Text.RegularExpressions;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Web.Constant.Parameter;

// ReSharper disable once CheckNamespace

namespace Web.Account
{
    public static class AccountManage
    {
        /// <summary>
        ///     用于连接Account的数据库连接字符串
        /// </summary>
        private static readonly String ConnectAccountDBString =
            Database.AccountConnectionString;

        /// <summary>
        ///     用于连接Expense的数据库连接字符串
        /// </summary>
        private static readonly String ConnectExpenseDBString =
            Database.ExpenseConnectionString;

        /// <summary>
        ///     获得当前账户的余额
        /// </summary>
        /// <param name="tableID">账户表</param>
        /// <returns>余额</returns>
        public static Decimal GetBalance(String tableID)
        {
            Decimal balance = 0;
            if (String.IsNullOrEmpty(tableID))
            {
                return 0;
            }
            var expenseConnect = new OleDbConnection(ConnectExpenseDBString);
            var calculateBalanceCMD = expenseConnect.CreateCommand();
            calculateBalanceCMD.CommandText = "SELECT SUM([Expense]) FROM [" + tableID + "]";

            try
            {
                expenseConnect.Open();
                var searchAccountReader = calculateBalanceCMD.ExecuteReader();
                if (searchAccountReader != null && searchAccountReader.Read())
                {
                    balance = searchAccountReader.GetDecimal(0);
                }
            }
            catch (Exception)
            {
                balance = 0;
            }
            finally
            {
                expenseConnect.Close();
            }
            return balance;
        }

        /// <summary>
        ///     获得当前账户的真实姓名
        /// </summary>
        /// <param name="tableID">账户表</param>
        /// <returns>真实姓名</returns>
        public static String GetRealName(String tableID)
        {
            if (String.IsNullOrEmpty(tableID))
            {
                return null;
            }
            String realName = null;
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            var searchAccountCMD = accountConnect.CreateCommand();
            searchAccountCMD.CommandText = "SELECT TOP 1 [RealName] FROM [Account] WHERE [TableID] = @TableID;";
            searchAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@TableID", tableID)
            });

            try
            {
                accountConnect.Open();
                var searchAccountReader = searchAccountCMD.ExecuteReader();
                if (searchAccountReader != null && searchAccountReader.Read())
                {
                    realName = searchAccountReader.GetString(searchAccountReader.GetOrdinal("RealName"));
                }
            }
            finally
            {
                accountConnect.Close();
            }
            return realName;
        }

        /// <summary>
        ///     获得当前账户的账户表
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>账户表</returns>
        public static String GetTableID(String userID)
        {
            if (String.IsNullOrEmpty(userID))
            {
                return null;
            }
            String tableID = null;
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            var searchAccountCMD = accountConnect.CreateCommand();
            searchAccountCMD.CommandText = "SELECT TOP 1 [TableID] FROM [Account] WHERE [UserID] = @UserID";
            searchAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@UserID", userID)
            });
            accountConnect.Open();
            try
            {
                var searchAccountReader = searchAccountCMD.ExecuteReader();
                if (searchAccountReader != null && searchAccountReader.Read())
                {
                    tableID = searchAccountReader.GetString(searchAccountReader.GetOrdinal("TableID"));
                }
            }
            finally
            {
                accountConnect.Close();
            }
            return tableID;
        }

        /// <summary>
        ///     获得当前账户的用户名
        /// </summary>
        /// <param name="email">Email</param>
        /// <returns>用户名</returns>
        public static String GetUserID(String email)
        {
            if (String.IsNullOrEmpty(email) || !Regex.IsMatch(email, @"^(\w)+(\.\w+)*@(\w)+((\.\w{2,3}){1,3})$"))
            {
                return null;
            }
            String userID = null;
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            var searchAccountCMD = accountConnect.CreateCommand();
            searchAccountCMD.CommandText = "SELECT TOP 1 [UserID] FROM [Account] WHERE [Email] = @Email";
            searchAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@Email", email)
            });
            accountConnect.Open();
            try
            {
                var searchAccountReader = searchAccountCMD.ExecuteReader();
                if (searchAccountReader != null && searchAccountReader.Read())
                {
                    userID = searchAccountReader.GetString(searchAccountReader.GetOrdinal("UserID"));
                }
            }
            finally
            {
                accountConnect.Close();
            }
            return userID;
        }

        /// <summary>
        ///     判断是否是管理员
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>结果</returns>
        public static Boolean IsAdministrator(String userID)
        {
            if (String.IsNullOrEmpty(userID))
            {
                return false;
            }
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            const string sql = "SELECT [Administrator] FROM [Account] WHERE [UserID] = @UserID;";
            var searchAccountCMD = new OleDbCommand(sql, accountConnect);
            searchAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@UserID", userID)
            }
                );

            var result = false;
            try
            {
                accountConnect.Open();
                var searchAccountReader = searchAccountCMD.ExecuteReader();
                if (searchAccountReader != null && searchAccountReader.Read())
                {
                    result = searchAccountReader.GetBoolean(searchAccountReader.GetOrdinal("Administrator"));
                }
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                accountConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     判断账户表是否存在
        /// </summary>
        /// <param name="tableID">账户表</param>
        /// <returns>结果</returns>
        public static Boolean IsExistTableID(String tableID)
        {
            if (String.IsNullOrEmpty(tableID))
            {
                return false;
            }
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            var searchAccountCMD = accountConnect.CreateCommand();
            searchAccountCMD.CommandText = "SELECT TOP 1 * FROM [Account] WHERE [TableID] = @TableID;";
            searchAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@TableID", tableID)
            });

            Boolean result;
            try
            {
                accountConnect.Open();
                var searchAccountReader = searchAccountCMD.ExecuteReader();
                result = searchAccountReader != null && searchAccountReader.HasRows;
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                accountConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     判断用户名是否存在
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>结果</returns>
        public static Boolean IsExistUserID(String userID)
        {
            if (String.IsNullOrEmpty(userID))
            {
                return false;
            }
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            var searchAccountCMD = accountConnect.CreateCommand();
            searchAccountCMD.CommandText = "SELECT TOP 1 * FROM [Account] WHERE [UserID] = @UserID;";
            searchAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@UserID", userID)
            });

            Boolean result;
            try
            {
                accountConnect.Open();
                var searchAccountReader = searchAccountCMD.ExecuteReader();
                result = searchAccountReader != null && searchAccountReader.HasRows;
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                accountConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     判断Email是否存在
        /// </summary>
        /// <param name="email">Email</param>
        /// <returns>结果</returns>
        public static Boolean IsExistEmail(String email)
        {
            if (String.IsNullOrEmpty(email))
            {
                return false;
            }
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            var searchAccountCMD = accountConnect.CreateCommand();
            searchAccountCMD.CommandText = "SELECT TOP 1 * FROM [Account] WHERE [Email] = @Email;";
            searchAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@Email", email)
            });

            Boolean result;
            try
            {
                accountConnect.Open();
                var searchAccountReader = searchAccountCMD.ExecuteReader();
                result = searchAccountReader != null && searchAccountReader.HasRows;
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                accountConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     验证账户
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="passwordSHA256">密码的SHA256加密值</param>
        /// <returns></returns>
        public static Boolean VerifyAccount(String userID, String passwordSHA256)
        {
            if (String.IsNullOrEmpty(userID) || String.IsNullOrEmpty(passwordSHA256))
            {
                return false;
            }
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            const string sql =
                "SELECT TOP 1 * FROM [Account] WHERE [UserID] = @UserID AND [Password] = @PasswordSHA256;";
            var searchAccountCMD = new OleDbCommand(sql, accountConnect);
            searchAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@UserID", userID),
                new OleDbParameter("@PasswordSHA256", passwordSHA256)
            });

            Boolean result;
            try
            {
                accountConnect.Open();
                var searchAccountReader = searchAccountCMD.ExecuteReader();
                result = searchAccountReader != null && searchAccountReader.HasRows;
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                accountConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     初始化用户
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="tableID">账户表</param>
        /// <param name="passwordSHA256">密码的SHA256加密值</param>
        /// <param name="email"></param>
        /// <returns>结果</returns>
        public static Boolean RegistAccount(String userID, String tableID, String passwordSHA256, String email)
        {
            if (String.IsNullOrEmpty(userID) || String.IsNullOrEmpty(tableID) || String.IsNullOrEmpty(passwordSHA256) ||
                String.IsNullOrEmpty(email))
            {
                return false;
            }
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            const string sql =
                "UPDATE [Account] SET [UserID] = @UserID, [Password] = @PasswordSHA256, [Email] = @Email, [Used] = 1 WHERE [TableID] = @TableID;";
            var updateAccountCMD = new OleDbCommand(sql, accountConnect);
            updateAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@UserID", userID),
                new OleDbParameter("@PasswordSHA256", passwordSHA256),
                new OleDbParameter("@Email", email),
                new OleDbParameter("@TableID", tableID)
            });
            Boolean result;
            try
            {
                accountConnect.Open();
                result = (updateAccountCMD.ExecuteNonQuery() >= 1);
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                accountConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     创建账户表
        /// </summary>
        /// <param name="tableID">账户表</param>
        /// <returns></returns>
        public static Boolean CreateTable(String tableID)
        {
            if (String.IsNullOrEmpty(tableID))
            {
                return false;
            }
            var expenseConnect = new OleDbConnection(ConnectExpenseDBString);
            var sql = "CREATE TABLE [" + tableID +
                      "] ([ID] AUTOINCREMENT PRIMARY KEY, [Time] DateTime, [Expense] Currency, [Item] Text);";
            var createTableCMD = new OleDbCommand(sql, expenseConnect);
            expenseConnect.Open();
            var expenseTransaction = expenseConnect.BeginTransaction();
            createTableCMD.Transaction = expenseTransaction;
            Boolean result;
            try
            {
                createTableCMD.ExecuteNonQuery();
                expenseTransaction.Commit();
                result = true;
            }
            catch (Exception)
            {
                expenseTransaction.Rollback();
                result = false;
            }
            finally
            {
                expenseConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     创建账户
        /// </summary>
        /// <param name="tableID">账户表</param>
        /// <param name="realName">真实姓名</param>
        /// <returns>结果</returns>
        public static Boolean CreateAccount(String tableID, String realName)
        {
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            const string sql = "INSERT INTO [Account] ([TableID], [RealName]) VALUES (@TableID, @RealName);";
            var createAccountCMD = new OleDbCommand(sql, accountConnect);
            createAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@TableID", tableID),
                new OleDbParameter("@RealName", realName)
            });

            accountConnect.Open();
            var accountTransaction = accountConnect.BeginTransaction();
            createAccountCMD.Transaction = accountTransaction;
            Boolean result;
            try
            {
                createAccountCMD.ExecuteNonQuery();
                accountTransaction.Commit();
                result = true;
            }
            catch (Exception)
            {
                accountTransaction.Rollback();
                result = false;
            }
            finally
            {
                accountConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     删除账户账户表
        /// </summary>
        /// <param name="tableID">账户表</param>
        /// <returns>结果</returns>
        public static Boolean DeleteTable(String tableID)
        {
            if (String.IsNullOrEmpty(tableID))
            {
                return false;
            }
            var expenseConnect = new OleDbConnection(ConnectExpenseDBString);
            var sql = "DROP TABLE [" + tableID + "];";
            var createTableCMD = new OleDbCommand(sql, expenseConnect);
            expenseConnect.Open();
            var expenseTransaction = expenseConnect.BeginTransaction();
            createTableCMD.Transaction = expenseTransaction;
            Boolean result;
            try
            {
                createTableCMD.ExecuteNonQuery();
                expenseTransaction.Commit();
                result = true;
            }
            catch (Exception)
            {
                expenseTransaction.Rollback();
                result = false;
            }
            finally
            {
                expenseConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     删除账户
        /// </summary>
        /// <param name="tableID">账户表</param>
        /// <returns>结果</returns>
        public static Boolean DeleteAccount(String tableID)
        {
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            const string sql = "DELETE FROM [Account] WHERE ([TableID] = @TableID);";
            var deleteAccountCMD = new OleDbCommand(sql, accountConnect);
            deleteAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@TableID", tableID)
            });

            accountConnect.Open();
            var accountTransaction = accountConnect.BeginTransaction();
            deleteAccountCMD.Transaction = accountTransaction;
            Boolean result;
            try
            {
                deleteAccountCMD.ExecuteNonQuery();
                accountTransaction.Commit();
                result = true;
            }
            catch (Exception)
            {
                accountTransaction.Rollback();
                result = false;
            }
            finally
            {
                accountConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     修改密码
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="passwordSHA256">密码的SHA256加密值</param>
        /// <returns>结果</returns>
        public static Boolean ChangePassword(String userID, String passwordSHA256)
        {
            if (String.IsNullOrEmpty(userID) || String.IsNullOrEmpty(passwordSHA256))
            {
                return false;
            }
            var accountConnect = new OleDbConnection(ConnectAccountDBString);
            const string sql = "Update [Account] SET [Password] = @PasswordSHA256 WHERE [UserID] = @UserID;";
            var updateAccountCMD = new OleDbCommand(sql, accountConnect);
            updateAccountCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@PasswordSHA256", passwordSHA256),
                new OleDbParameter("@UserID", userID)
            });
            Boolean result;
            try
            {
                accountConnect.Open();
                result = (updateAccountCMD.ExecuteNonQuery() >= 1);
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                accountConnect.Close();
            }
            return result;
        }

        /// <summary>
        ///     交易记录导出为Excel
        /// </summary>
        /// <param name="tableID">账户表</param>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">截止日期</param>
        /// <returns>Excel文件</returns>
        public static MemoryStream ExportToExcel(String tableID, DateTime startDate, DateTime endDate)
        {
            MemoryStream excelMemoryStream;
            var expenseConnect = new OleDbConnection(ConnectExpenseDBString);
            var sql = "SELECT [Time], [Expense], [Item] FROM [" + tableID +
                      "] WHERE ([Time] >= @StartDate) AND ([Time] <= @EndDate) ORDER BY [Time] DESC;";
            var exportDataCMD = new OleDbCommand(sql, expenseConnect);
            exportDataCMD.Parameters.AddRange(new[]
            {
                new OleDbParameter("@StartDate", startDate),
                new OleDbParameter("@EndDate", endDate.AddDays(1))
            });
            try
            {
                expenseConnect.Open();
                var exportDataReader = exportDataCMD.ExecuteReader();

                excelMemoryStream = new MemoryStream();
                var excelBook = new HSSFWorkbook();
                var excelSheet = excelBook.CreateSheet(GetRealName(tableID));
                excelSheet.SetColumnWidth(0, Convert.ToInt32(Math.Round(23.25*256)));

                var titleFont = excelBook.CreateFont();
                titleFont.FontName = "宋体";
                titleFont.FontHeightInPoints = 12;
                titleFont.Boldweight = (short) FontBoldWeight.Bold;
                var titleCellStyle = excelBook.CreateCellStyle();
                titleCellStyle.SetFont(titleFont);
                titleCellStyle.Alignment = HorizontalAlignment.Center;

                var excelRow = excelSheet.CreateRow(0);

                var excelCell = excelRow.CreateCell(0);
                excelCell.SetCellValue("时间");
                excelCell.CellStyle = titleCellStyle;

                excelCell = excelRow.CreateCell(1);
                excelCell.SetCellValue("金额");
                excelCell.CellStyle = titleCellStyle;

                excelCell = excelRow.CreateCell(2);
                excelCell.SetCellValue("项目");
                excelCell.CellStyle = titleCellStyle;

                var itemFont = excelBook.CreateFont();
                itemFont.FontName = "宋体";
                itemFont.FontHeightInPoints = 12;
                var itemCellStyle = excelBook.CreateCellStyle();
                itemCellStyle.SetFont(itemFont);

                var excelCount = 1;
                while (exportDataReader != null && exportDataReader.Read())
                {
                    excelRow = excelSheet.CreateRow(excelCount);

                    excelCell = excelRow.CreateCell(0);
                    excelCell.SetCellValue(
                        exportDataReader.GetDateTime(exportDataReader.GetOrdinal("Time"))
                            .ToString("yyyy/MM/dd HH:mm:ss"));
                    excelCell.CellStyle = itemCellStyle;

                    excelCell = excelRow.CreateCell(1);
                    excelCell.SetCellValue(
                        exportDataReader.GetDecimal(exportDataReader.GetOrdinal("Expense")).ToString("N2"));
                    excelCell.CellStyle = itemCellStyle;
                    excelCell.CellStyle.Alignment = HorizontalAlignment.Right;

                    excelCell = excelRow.CreateCell(2);
                    excelCell.SetCellValue(exportDataReader.GetString(exportDataReader.GetOrdinal("Item")));
                    excelCell.CellStyle = itemCellStyle;
                    excelCell.CellStyle.Alignment = HorizontalAlignment.Left;

                    excelCount++;
                }
                if (exportDataReader != null && exportDataReader.HasRows)
                {
                    for (var i = 1; i < exportDataReader.FieldCount; i++)
                    {
                        excelSheet.AutoSizeColumn(i);
                    }
                    excelBook.Write(excelMemoryStream);
                }
            }
            catch (Exception)
            {
                excelMemoryStream = null;
            }
            finally
            {
                expenseConnect.Close();
            }
            return excelMemoryStream;
        }
    }
}